package common; 
import java.sql.*;
import java.io.*;
import java.util.*;

public class Agent_sql_run_mail {

  public static void main(String args[])throws Exception {
    String DB_IP = "";
    String DB_PORT = "";
    String DB_NAME = "";
    String DB_USER = "";
    String DB_PASSWORD = "";
    
    String DB_IP_MAIL = "";
    String DB_PORT_MAIL = "";
    String DB_NAME_MAIL = "";
    String DB_USER_MAIL = "";
    String DB_PASSWORD_MAIL = "";
    
    String ConfigFile = "./dbconfig.db"; 
	
    String RUN_SQL = "";
    String MAIL_IDX = "";
	
    Connection DBCon= null;
    Statement  stmt = null;
    ResultSet  rs   = null;
    
    
    Connection DBCon_MAIL= null;
    Statement  stmt_MAIL = null;
    ResultSet  rs_MAIL   = null;
	
    String inDate   = new java.text.SimpleDateFormat("yyyyMMdd").format(new java.util.Date());
    String inTime   = new java.text.SimpleDateFormat("HHmmss").format(new java.util.Date());
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");	
      Class.forName("com.mysql.jdbc.Driver");
      Properties config = new Properties();
      config.load(new FileInputStream(ConfigFile));
      
      DB_IP       = config.getProperty("DB_IP");
      DB_PORT     = config.getProperty("DB_PORT");
      DB_NAME     = Crypto.DecodeBySType(config.getProperty("DB_NAME"));
      DB_USER     = Crypto.DecodeBySType(config.getProperty("DB_USER"));
      DB_PASSWORD = Crypto.DecodeBySType(config.getProperty("DB_PASSWORD"));
      
      DB_IP_MAIL       = config.getProperty("DB_IP_MAIL");
      DB_PORT_MAIL     = config.getProperty("DB_PORT_MAIL");
      DB_NAME_MAIL     = Crypto.DecodeBySType(config.getProperty("DB_NAME_MAIL"));
      DB_USER_MAIL     = Crypto.DecodeBySType(config.getProperty("DB_USER_MAIL"));
      DB_PASSWORD_MAIL = Crypto.DecodeBySType(config.getProperty("DB_PASSWORD_MAIL"));
      
      RUN_SQL = args[0];
      MAIL_IDX = args[1];

      DBCon = DriverManager.getConnection("jdbc:mysql://"+ DB_IP +":"+ DB_PORT +"/"+ DB_NAME ,DB_USER,DB_PASSWORD);
      stmt = DBCon.createStatement();
      
      rs = stmt.executeQuery("call "+ RUN_SQL);
      
      DBCon_MAIL = DriverManager.getConnection("jdbc:sqlserver://"+ DB_IP_MAIL +":"+ DB_PORT_MAIL +";DatabaseName="+ DB_NAME_MAIL ,DB_USER_MAIL,DB_PASSWORD_MAIL);
      stmt_MAIL = DBCon_MAIL.createStatement();
	  
      
      /*
      101 일반
      102 MAIL_STD_COURSE_PETITION_SEND(in v_day_interval integer) 수강신청기간 안내 - 완료
      103 MAIL_STD_FINALTEST_URGE_MSG_SEND() 최종평가 응시 남은 기간 알림  - 완료
      104 MAIL_STD_FIN_MSG_SEND(in v_day_interval integer) 수료처리 알림 - 완료
      105 MAIL_STD_JINDO_URGE_MSG_SEND() 학습진도 부진 알림 - 완료
      106 MAIL_STD_START_MSG_SEND() 학습시작일 알림 - 완료
      107 MAIL_STD_START_URGE_MSG_SEND(in v_day_interval integer) 학습진행 안하고 있다는거 알림 - 완료
      */
      

      if (rs != null) {
        /*
    	ResultSetMetaData rsmd = rs.getMetaData(); 
        int columnCount = rsmd.getColumnCount(); 
        String temp = "[" ;

        while (rs.next()){
          for (int i = 0; i < columnCount; i++){
            temp += rsmd.getColumnName(i+1) +":"+ rs.getString(rsmd.getColumnName(i+1)) +"|";
          }
        }
        temp +="]";
        System.out.println(inDate + inTime +":"+ RUN_SQL+" excuted at =="+ temp );
        */
    	  String poi_nm = null;
    	  String login_nm = null;
    	  String std_dt = null;
    	  String final_prog_rate = null;
    	  String rpt_point = null;
    	  String final_eval_point = null;
    	  String discuss_point = null;
    	  String tot_point = null;
    	  String fin_base_point = null;
    	  String fin_base_prog_rate = null;
    	  String fin_whether = null;
    	  String fin_base_yn = null;
    	  String progress_eval_base = null;
    	  String buildup_eval_base = null;
    	  String rpt_eval_base = null;
    	  String final_eval_base = null;
    	  String discuss_eval_base = null;
    	  String email_id = null;
    	  String train_URL = null;
    	  String msg = null;
    	  while( rs.next() ) {
    		  poi_nm = rs.getString("poi_nm2"); //
    		  login_nm = rs.getString("login_nm"); //
    		  std_dt = rs.getString("std_dt"); //
    		  final_prog_rate = rs.getString("final_prog_rate"); //
    		  rpt_point = rs.getString("rpt_point"); //
    		  final_eval_point = rs.getString("final_eval_point"); //
    		  discuss_point = rs.getString("discuss_point"); //
    		  tot_point = rs.getString("tot_point"); 
    		  fin_base_point = rs.getString("fin_base_point"); //
    		  fin_base_prog_rate = rs.getString("fin_base_prog_rate"); //
    		  fin_whether = rs.getString("fin_whether");
    		  fin_base_yn = rs.getString("fin_base_yn");
    		  progress_eval_base = rs.getString("progress_eval_base"); //
    		  buildup_eval_base = rs.getString("buildup_eval_base"); 
    		  rpt_eval_base = rs.getString("rpt_eval_base"); // 
    		  final_eval_base = rs.getString("final_eval_base"); //
    		  discuss_eval_base = rs.getString("discuss_eval_base"); //
    		  email_id = rs.getString("email_id"); //
    		  train_URL = rs.getString("train_URL"); //
    		  msg = rs.getString("msg"); //
    		  
    		  //email_id = "gongmeung@hanmail.net";
    		  
    		  System.out.println(inDate + inTime +":"+" 발송내역 ==> msg="+msg+" email_id="+email_id);
    		  
    		  CallableStatement Cs = null;
    			Cs = DBCon_MAIL.prepareCall("{call netpion.HSP_EDUBOXHRD_AUTOMAIL_INTERFACE_INSERT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");        
    			String val1 = email_id.split("@")[0];
    			String val2 = MAIL_IDX; /*[일반메일타입]오토메일 보내는거 구분값 다른거 할려면 숫자변경해서 하기 오토메일에서 셀렉트 쿼리문에서 바꾸면 됨*/
    			String val3 = email_id;
    			String val4 = login_nm;
    			String val5 = ""; /*제목 넣는 부분*/
    			String val6 = msg;
    			String val_null = null;
    			
    			Cs.setString(1,val1); 
    			Cs.setString(2,val2); 
    			Cs.setString(3,val3); 
    			Cs.setString(4,val4); 
    			Cs.setString(5,val_null); 
    			Cs.setString(6,val_null); 
    			Cs.setString(7,val_null); 
    			Cs.setString(8,val_null); 
    			Cs.setString(9,val_null); 
    			Cs.setString(10,val_null); 
    			Cs.setString(11,val_null); 
    			Cs.setString(12,val_null); 
    			Cs.setString(13,val5); 
    			Cs.setString(14,poi_nm); //TAG1
    			Cs.setString(15,std_dt); //TAG2
    			Cs.setString(16,final_prog_rate); //TAG3
    			Cs.setString(17,rpt_point); //TAG4
    			Cs.setString(18,final_eval_point); //TAG5
    			Cs.setString(19,discuss_point); //TAG6
    			Cs.setString(20,progress_eval_base); //TAG7
    			Cs.setString(21,rpt_eval_base); //TAG8
    			Cs.setString(22,final_eval_base); //TAG9
    			Cs.setString(23,discuss_eval_base); //TAG10
    			Cs.setString(24,fin_base_point); //TAG11
    			Cs.setString(25,fin_base_prog_rate); //TAG12
    			Cs.setString(26,tot_point); //TAG13
    			Cs.setString(27,train_URL); //TAG14
    			Cs.setString(28,msg); //TAG15
    			Cs.setString(29,val_null); //TAG16
    			Cs.setString(30,val_null); //TAG17
    			Cs.setString(31,val_null); //TAG18
    			Cs.setString(32,val_null); //TAG19
    			Cs.setString(33,val_null); //TAG20
    			Cs.execute();
    	  }
      } else {
        System.out.println(inDate + inTime +":"+ RUN_SQL+" excuted at == NULL");	    	
      }
    }catch(IOException e)  {	    
      System.out.println(inDate + inTime +":IOException= "+e.getMessage());
    }catch(SQLException e) {
      System.out.println(inDate + inTime +":SQLException= "+e.getMessage());
    }catch(Exception e) {
      System.out.println(inDate + inTime +":Exception= "+e.getMessage());
    }finally {
      try{
        if (rs   != null) rs.close();
        if (stmt != null) stmt.close();
        if (DBCon!= null) DBCon.close();
        
        if (rs_MAIL   != null) rs_MAIL.close();
        if (stmt_MAIL != null) stmt_MAIL.close();
        if (DBCon_MAIL!= null) DBCon_MAIL.close();
      }catch(Exception ee){}
    }
  }
}
